from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
from sqlalchemy import Column, Integer, String, Float, DateTime, ForeignKey, create_engine, text
from sqlalchemy.orm import relationship

db = SQLAlchemy()
Base = db.Model

class Material(Base):
    __tablename__ = 'materials'
    
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False, comment='材料名称')
    specification = Column(String(200), comment='规格')
    brand = Column(String(100), comment='品牌')
    model = Column(String(100), comment='型号')
    unit = Column(String(20), comment='单位')
    price = Column(Float, nullable=False, comment='单价')
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

class Process(Base):
    __tablename__ = 'processes'
    
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False, comment='工序名称')
    unit = Column(String(20), comment='计价单位')
    price = Column(Float, nullable=False, comment='单价')
    description = Column(String(200), comment='工序描述')
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

class Product(Base):
    __tablename__ = 'products'
    
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False, comment='产品名称')
    category = Column(String(50), comment='产品类别')
    specification = Column(String(200), comment='规格')
    material_cost = Column(Float, default=0, comment='材料成本')
    labor_cost = Column(Float, default=0, comment='人工成本')
    total_price = Column(Float, default=0, comment='总价')
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

class ProductMaterial(Base):
    __tablename__ = 'product_materials'
    
    id = Column(Integer, primary_key=True)
    product_id = Column(Integer, ForeignKey('products.id'))
    material_id = Column(Integer, ForeignKey('materials.id'))
    quantity = Column(Float, nullable=False, comment='用量')
    subtotal = Column(Float, default=0, comment='小计')
    
    product = relationship('Product', backref='materials')
    material = relationship('Material')

class ProductProcess(Base):
    __tablename__ = 'product_processes'
    
    id = Column(Integer, primary_key=True)
    product_id = Column(Integer, ForeignKey('products.id'))
    process_id = Column(Integer, ForeignKey('processes.id'))
    quantity = Column(Float, nullable=False, comment='工序数量')
    subtotal = Column(Float, default=0, comment='小计')
    
    product = relationship('Product', backref='processes')
    process = relationship('Process')

def init_db():
    # 首先创建数据库连接（不指定数据库）
    engine = create_engine('mysql+pymysql://root:123456@127.0.0.1/')
    
    # 创建数据库（如果不存在）
    with engine.connect() as conn:
        conn.execute(text('CREATE DATABASE IF NOT EXISTS product_pricing'))
        conn.execute(text('USE product_pricing'))
    
    # 重新创建带有数据库的连接
    engine = create_engine('mysql+pymysql://root:123456@127.0.0.1/product_pricing')
    
    # 创建所有表
    Base.metadata.create_all(engine)
    return engine